package com.william.jdbcplus.core.dialect.meta.impl;

import com.william.jdbcplus.core.dialect.meta.IMeta;
import com.william.jdbcplus.core.toolkit.StringPool;
import com.william.jdbcplus.core.util.datameta.TableMetaFkInfo;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 */
public class OracleMeta extends BaseMeta implements IMeta {

    @Override
    public List<TableMetaFkInfo> getFkMeta(DataSource ds, String tableName) {
        List<TableMetaFkInfo> result = new ArrayList<>();
        Connection connection = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            connection = ds.getConnection();
            StringBuffer sqlString = new StringBuffer();
            sqlString
                    .append("Select")
                    .append(StringPool.NEWLINE).append("a.Table_Name as fktable_name,")
                    .append(StringPool.NEWLINE).append("c.Column_Name as fkcolumn_name,")
                    .append(StringPool.NEWLINE).append("b.Table_Name as pktable_name,")
                    .append(StringPool.NEWLINE).append("d.Column_Name as pkcolumn_name,")
                    .append(StringPool.NEWLINE).append("c.Constraint_Name as fk_name,")
                    .append(StringPool.NEWLINE).append("d.Constraint_Name as pk_name,")
                    .append(StringPool.NEWLINE).append("c.position as key_seq")
                    .append(StringPool.NEWLINE).append("From")
                    .append(StringPool.NEWLINE).append("user_Constraints a,")
                    .append(StringPool.NEWLINE).append("user_Constraints b,")
                    .append(StringPool.NEWLINE).append("user_Cons_Columns c,")
                    .append(StringPool.NEWLINE).append("user_Cons_Columns d")
                    .append(StringPool.NEWLINE).append("Where")
                    .append(StringPool.NEWLINE).append("a.r_Constraint_Name = b.Constraint_Name")
                    .append(StringPool.NEWLINE).append("And a.Constraint_Type = 'R'")
                    .append(StringPool.NEWLINE).append("And a.r_Owner = b.Owner")
                    .append(StringPool.NEWLINE).append("And a.Constraint_Name = c.Constraint_Name")
                    .append(StringPool.NEWLINE).append("And b.Constraint_Name = d.Constraint_Name")
                    .append(StringPool.NEWLINE).append("And a.Owner = c.Owner")
                    .append(StringPool.NEWLINE).append("And a.Table_Name = c.Table_Name")
                    .append(StringPool.NEWLINE).append("And b.Owner = d.Owner")
                    .append(StringPool.NEWLINE).append("And b.Table_Name = d.Table_Name")
                    .append(StringPool.NEWLINE).append("And a.table_name = :1");

            ps = connection.prepareStatement(sqlString.toString());
            ps.setString(1, tableName);
            rs = ps.executeQuery();
            while (rs.next()) {
                String pkTableName = rs.getString("PKTABLE_NAME");
                String pkColumnName = rs.getString("PKCOLUMN_NAME");
                String fkTableName = rs.getString("FKTABLE_NAME");
                String fkColumnName = rs.getString("FKCOLUMN_NAME");
                short keySeq = rs.getShort("KEY_SEQ");
                String fkName = rs.getString("FK_NAME");
                String pkName = rs.getString("PK_NAME");
                TableMetaFkInfo fkInfo = TableMetaFkInfo.builder()
                        .tableName(fkTableName)
                        .pkTableName(pkTableName)
                        .pkColumnName(pkColumnName)
                        .fkTableName(fkTableName)
                        .fkColumnName(fkColumnName)
                        .fkName(fkName)
                        .pkName(pkName)
                        .keySeq(keySeq)
                        .build();
                result.add(fkInfo);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != rs) {
                    rs.close();
                }
                if (null != ps) {
                    ps.close();
                }
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException ex) {
            }
        }

        return result;
    }

    @Override
    public List<TableMetaFkInfo> getFkMetaList(DataSource ds) {
        List<TableMetaFkInfo> result = new ArrayList<>();
        Connection connection = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            connection = ds.getConnection();
            StringBuffer sqlString = new StringBuffer();
            sqlString
                    .append("Select")
                    .append(StringPool.NEWLINE).append("a.Table_Name as fktable_name,")
                    .append(StringPool.NEWLINE).append("c.Column_Name as fkcolumn_name,")
                    .append(StringPool.NEWLINE).append("b.Table_Name as pktable_name,")
                    .append(StringPool.NEWLINE).append("d.Column_Name as pkcolumn_name,")
                    .append(StringPool.NEWLINE).append("c.Constraint_Name as fk_name,")
                    .append(StringPool.NEWLINE).append("d.Constraint_Name as pk_name,")
                    .append(StringPool.NEWLINE).append("c.position as key_seq")
                    .append(StringPool.NEWLINE).append("From")
                    .append(StringPool.NEWLINE).append("user_Constraints a,")
                    .append(StringPool.NEWLINE).append("user_Constraints b,")
                    .append(StringPool.NEWLINE).append("user_Cons_Columns c,")
                    .append(StringPool.NEWLINE).append("user_Cons_Columns d")
                    .append(StringPool.NEWLINE).append("Where")
                    .append(StringPool.NEWLINE).append("a.r_Constraint_Name = b.Constraint_Name")
                    .append(StringPool.NEWLINE).append("And a.Constraint_Type = 'R'")
                    .append(StringPool.NEWLINE).append("And a.r_Owner = b.Owner")
                    .append(StringPool.NEWLINE).append("And a.Constraint_Name = c.Constraint_Name")
                    .append(StringPool.NEWLINE).append("And b.Constraint_Name = d.Constraint_Name")
                    .append(StringPool.NEWLINE).append("And a.Owner = c.Owner")
                    .append(StringPool.NEWLINE).append("And a.Table_Name = c.Table_Name")
                    .append(StringPool.NEWLINE).append("And b.Owner = d.Owner")
                    .append(StringPool.NEWLINE).append("And b.Table_Name = d.Table_Name");

            ps = connection.prepareStatement(sqlString.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                String pkTableName = rs.getString("PKTABLE_NAME");
                String pkColumnName = rs.getString("PKCOLUMN_NAME");
                String fkTableName = rs.getString("FKTABLE_NAME");
                String fkColumnName = rs.getString("FKCOLUMN_NAME");
                short keySeq = rs.getShort("KEY_SEQ");
                String fkName = rs.getString("FK_NAME");
                String pkName = rs.getString("PK_NAME");
                TableMetaFkInfo fkInfo = TableMetaFkInfo.builder()
                        .tableName(fkTableName)
                        .pkTableName(pkTableName)
                        .pkColumnName(pkColumnName)
                        .fkTableName(fkTableName)
                        .fkColumnName(fkColumnName)
                        .fkName(fkName)
                        .pkName(pkName)
                        .keySeq(keySeq)
                        .build();
                result.add(fkInfo);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != rs) {
                    rs.close();
                }
                if (null != ps) {
                    ps.close();
                }
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException ex) {
            }
        }

        return result;
    }
}
